#include "statistiques.h"

Statistiques::Statistiques(QObject *parent, QSqlDatabase database) :
    QObject(parent)
{
    db = database;
    if(!db.open()){
        db = QSqlDatabase::database("guest");
    }
}

PromsModel *Statistiques::consoMax(int id){
    return consoMax("",id);
}


PromsModel *Statistiques::consoMax(QString recherche, int id){

    QSettings s;

    int cat = s.value("categorie_racine").toInt();

    QSqlDatabase db = QSqlDatabase::database("guest");
    db.transaction();
    QSqlQuery q(db);

    if(id > 0){
        //Si le pg existe, on récupère la liste des consos du Pg, groupés par Intitulés, triées par nombre decroissant.
        q.prepare("SELECT c.id, c.nom, c.prix, COUNT(*) AS nb FROM v_consos AS c LEFT JOIN (SELECT * FROM v_hist WHERE compte=:id) AS h on c.id=h.conso WHERE c.motsclefs LIKE :mot AND get_racine(idcategorie)=:cat GROUP BY c.id  ORDER BY nb DESC LIMIT 0 , 12");
        q.bindValue(":id",id);
    }else{
        q.prepare("SELECT c.id, c.nom, c.prix, COUNT(*) AS nb FROM v_consos AS c LEFT JOIN (SELECT * FROM v_hist) AS h on c.id=h.conso WHERE c.motsclefs LIKE :mot AND get_racine(idcategorie)=:cat GROUP BY c.id  ORDER BY nb DESC LIMIT 0 , 12");
    }
    q.bindValue(":mot","%"+recherche+"%");
    q.bindValue(":cat", cat);
    q.exec();

    PromsModel * model = new PromsModel(0);
    model->setQuery(q);

    return model;

}


int Statistiques::dernieresConsos(QList<int> *pgs, QList<QStringList *> *Consos, int limite){

    QSettings s;
    int cat = s.value("categorie_racine").toInt();

    QSqlDatabase db = QSqlDatabase::database("guest");
    QSqlQuery q(db);
    //SELECT h.hdate, h.compte, h.conso, c.nom, COUNT(*) FROM v_hist AS h INNER JOIN v_consos AS c ON h.conso = c.id WHERE get_racine(c.idcategorie) = :cat GROUP BY h.hdate DESC, h.compte, h.conso LIMIT 0,:lim
    q.prepare("SELECT compte, intitule, COUNT(*) FROM v_hist, ( SELECT id FROM v_consos WHERE get_racine(idcategorie)=:cat ) AS c WHERE c.id=conso GROUP BY hdate, compte, intitule ORDER BY hdate DESC LIMIT :lim");
    q.bindValue(":lim",10*limite);
    q.bindValue(":cat",cat);
    q.exec();

    int i = 0;
    if(q.first()){
        while(i < limite){
            int pg = q.value(0).toInt();
            QStringList *l = new QStringList();
            while(q.value(0).toInt() == pg){
                l->append(q.value(2).toString()+" x "+q.value(1).toString());
                if(!q.next()){
                    pgs->append(pg);
                    Consos->append(l);
                    return i+1;
                }
            }
            pgs->append(pg);
            Consos->append(l);
            i++;
        }
    }

    return i;
}

int Statistiques::grosConsommateurs(QList<int> *pgs, QList<double> *somme, int limite){
    QSqlDatabase db = QSqlDatabase::database("guest");
    QSqlQuery q(db);


    QSettings s;
    int cat = s.value("categorie_racine").toInt();

    //SELECT h.compte, SUM(h.montant) AS s FROM v_hist AS h INNER JOIN v_consos AS c ON h.conso = c.id WHERE get_racine(c.idcategorie) = :cat GROUP BY compte ORDER BY s DESC LIMIT 0,:lim
    q.prepare("SELECT compte, SUM(montant) AS s FROM v_hist, ( SELECT id FROM v_consos WHERE get_racine(idcategorie)=:cat ) AS c WHERE c.id=conso GROUP BY compte ORDER BY s DESC LIMIT :lim");
    q.bindValue(":lim",limite);
    q.bindValue(":cat",cat);
    q.exec();

    int i = 0;
    while(q.next()){
        pgs->append(q.value(0).toInt());
        somme->append(q.value(1).toDouble());
        i++;
    }

    return i;
}
